library(tidyverse)
library(readxl)

county_data <- tibble() # start with an empty df

##### Read each Leip file & clean #####

for(year in seq(2004, 2016, 2)) {
  county_year <- read_xlsx(paste("leip/Turnout_Data_", year, ".xlsx", sep = ""), sheet = "County VTO") %>% 
    janitor::clean_names()
  
  # Deal with files with weird column names
  
  if(year == 2012) {
    county_year <- county_year %>% 
      rename(total_registered = total_reg,
             turnout = total_vote)
  }
  
  if(year == 2016) {
    county_year <- county_year %>% 
      rename(total_registered = total_reg)
  }
  county_year <- county_year %>% 
    rename(countyname = 1, stateabbr = 2,
           total_voting = turnout) %>% 
    mutate(year = year) %>% # indicator column for year
    drop_na(fips) %>% 
    filter(stateabbr != "T") %>% # filter out state totals
    select(countyname, stateabbr, year, fips, total_voting, total_registered)
  county_data <- county_data %>% 
    bind_rows(county_year) # stack up all the data
}

##### Pivot data wider #####

county_data_wide <- county_data %>% 
  pivot_wider(names_from = year, values_from = c(total_registered, total_voting)) %>% 
  arrange(stateabbr, countyname)

write_rds(county_data_wide, "dataset_leip.rds")
